# 02_aarrr_analysis.py - AARRR用户生命周期分析
import pandas as pd
import sqlite3


def aarrr_analysis(csv_file_path):
    """
    AARRR用户生命周期分析
    输入: CSV文件路径
    输出: AARRR各阶段分析结果的CSV文件
    """
    print("=== 开始AARRR用户生命周期分析 ===")

    # 读取数据
    df = pd.read_csv(csv_file_path)
    conn = sqlite3.connect(':memory:')
    df.to_sql('user_personalized_features', conn, index=False, if_exists='replace')

    # 1. 获取阶段分析
    print("1. 获取阶段分析...")
    acquisition_sql = """
    SELECT 
        Location AS 用户来源,
        COUNT(*) AS 用户数量,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user_personalized_features), 2) AS 占比百分比,
        ROUND(AVG(Total_Spending), 2) AS 平均消费,
        ROUND(AVG(Purchase_Frequency), 2) AS 平均购买频率,
        ROUND(AVG(Time_Spent_on_Site_Minutes), 2) AS 平均停留时间
    FROM user_personalized_features
    GROUP BY Location
    ORDER BY 用户数量 DESC;
    """
    acquisition_result = pd.read_sql_query(acquisition_sql, conn)

    # 2. 激活阶段分析
    print("2. 激活阶段分析...")
    activation_sql = """
    SELECT 
        CASE 
            WHEN Newsletter_Subscription = 1 THEN '已订阅'
            ELSE '未订阅'
        END AS 订阅状态,
        COUNT(*) AS 用户数量,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user_personalized_features), 2) AS 占比百分比,
        ROUND(AVG(Time_Spent_on_Site_Minutes), 2) AS 平均停留时间,
        ROUND(AVG(Pages_Viewed), 2) AS 平均浏览页数,
        ROUND(AVG(Total_Spending), 2) AS 平均消费,
        ROUND(AVG(Purchase_Frequency), 2) AS 平均购买频率
    FROM user_personalized_features
    GROUP BY Newsletter_Subscription;
    """
    activation_result = pd.read_sql_query(activation_sql, conn)

    # 3. 留存阶段分析
    print("3. 留存阶段分析...")
    retention_sql = """
    SELECT 
        CASE 
            WHEN Last_Login_Days_Ago <= 7 THEN '7天内活跃'
            WHEN Last_Login_Days_Ago <= 30 THEN '30天内活跃'
            WHEN Last_Login_Days_Ago <= 90 THEN '90天内活跃'
            ELSE '沉睡用户'
        END AS 活跃等级,
        COUNT(*) AS 用户数量,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user_personalized_features), 2) AS 占比百分比,
        ROUND(AVG(Last_Login_Days_Ago), 2) AS 平均未登录天数,
        ROUND(AVG(Purchase_Frequency), 2) AS 平均购买频率,
        ROUND(AVG(Total_Spending), 2) AS 平均消费金额,
        ROUND(AVG(Time_Spent_on_Site_Minutes), 2) AS 平均停留时间
    FROM user_personalized_features
    GROUP BY 活跃等级
    ORDER BY 
        CASE 活跃等级
            WHEN '7天内活跃' THEN 1
            WHEN '30天内活跃' THEN 2
            WHEN '90天内活跃' THEN 3
            ELSE 4
        END;
    """
    retention_result = pd.read_sql_query(retention_sql, conn)

    # 4. 收入阶段分析
    print("4. 收入阶段分析...")
    revenue_sql = """
    SELECT 
        Product_Category_Preference AS 产品品类,
        COUNT(*) AS 用户数量,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user_personalized_features), 2) AS 用户占比,
        ROUND(SUM(Total_Spending), 2) AS 总消费金额,
        ROUND(AVG(Total_Spending), 2) AS 平均消费,
        ROUND(AVG(Purchase_Frequency), 2) AS 平均购买频率,
        ROUND(AVG(Average_Order_Value), 2) AS 平均客单价
    FROM user_personalized_features
    GROUP BY Product_Category_Preference
    ORDER BY 总消费金额 DESC;
    """
    revenue_result = pd.read_sql_query(revenue_sql, conn)

    # 5. 推荐阶段分析 - 简化版本
    print("5. 推荐阶段分析...")
    referral_sql = """
    WITH UserStats AS (
        SELECT 
            User_ID,
            Total_Spending,
            Purchase_Frequency,
            Last_Login_Days_Ago,
            -- 计算综合价值分数
            (Total_Spending * 0.5 + Purchase_Frequency * 30 + 
             (CASE WHEN Last_Login_Days_Ago <= 30 THEN 100 ELSE 0 END)) AS Value_Score
        FROM user_personalized_features
    )
    SELECT 
        CASE 
            WHEN Value_Score > (SELECT AVG(Value_Score) * 1.5 FROM UserStats) THEN '高价值用户'
            WHEN Value_Score > (SELECT AVG(Value_Score) FROM UserStats) THEN '中价值用户'
            ELSE '普通用户'
        END AS 用户价值等级,
        COUNT(*) AS 用户数量,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user_personalized_features), 2) AS 占比百分比,
        ROUND(AVG(Total_Spending), 2) AS 平均消费,
        ROUND(AVG(Purchase_Frequency), 2) AS 平均购买频率
    FROM UserStats
    GROUP BY 用户价值等级
    ORDER BY 平均消费 DESC;
    """
    referral_result = pd.read_sql_query(referral_sql, conn)

    # 保存所有AARRR分析结果
    acquisition_result.to_csv('aarrr_acquisition.csv', index=False, encoding='utf-8-sig')
    activation_result.to_csv('aarrr_activation.csv', index=False, encoding='utf-8-sig')
    retention_result.to_csv('aarrr_retention.csv', index=False, encoding='utf-8-sig')
    revenue_result.to_csv('aarrr_revenue.csv', index=False, encoding='utf-8-sig')
    referral_result.to_csv('aarrr_referral.csv', index=False, encoding='utf-8-sig')

    print("AARRR分析完成！")
    print("结果文件已保存:")
    print("- aarrr_acquisition.csv (获取阶段)")
    print("- aarrr_activation.csv (激活阶段)")
    print("- aarrr_retention.csv (留存阶段)")
    print("- aarrr_revenue.csv (收入阶段)")
    print("- aarrr_referral.csv (推荐阶段)")

    conn.close()

    return {
        'acquisition': acquisition_result,
        'activation': activation_result,
        'retention': retention_result,
        'revenue': revenue_result,
        'referral': referral_result
    }


if __name__ == "__main__":
    # 使用示例
    aarrr_analysis('user_personalized_features.csv')